package com.estore.daoImpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.estore.dao.ShopDao;
import com.estore.entity.AgentBanner;
import com.estore.entity.Agent_Product;
import com.estore.entity.Agent_Profile;
import com.estore.entity.Discount;
import com.estore.entity.ProductList;
import com.estore.entity.ShopInterface;
import com.estore.info.AgentProductInfo;
import com.estore.info.DiscountInfo;
import com.estore.info.ShopInfo;
import com.estore.mapper.AgentBannerMapper;
import com.estore.mapper.AgentProductInfoMapper;
import com.estore.mapper.Agent_ProductMapper;
import com.estore.mapper.Agent_ProfileMapper;
import com.estore.mapper.DiscountInfoMapper;
import com.estore.mapper.DiscountMapper;
import com.estore.mapper.ProductListMapper;
import com.estore.mapper.ShopInfoMapper;
import com.estore.mapper.ShopInterfaceMapper;

public class ShopDaoImpl implements ShopDao {
	private DataSource dataSource;
	private JdbcTemplate templateObject;

	@Override
	public void setDataSource(DataSource datasource) {
		this.dataSource = datasource;
		this.templateObject = new JdbcTemplate(datasource);

	}

	@Override
	public boolean checkAgentAvail(Integer userID) {
		String sql = "select * from agent_profile where userid = ?";
		Object[] list = new String[] { userID.toString() };
		try {
			templateObject.queryForObject(sql, list, new Agent_ProfileMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	public JdbcTemplate getTemplateObject() {
		return templateObject;
	}

	public void setTemplateObject(JdbcTemplate templateObject) {
		this.templateObject = templateObject;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	@Override
	public void regShop(ShopInfo info) {
		String sql = "insert into agent_profile (userid, agentName, address, phonenumber, agentemail,"
				+ " agentwebsite, startdate, status, logo) value (?, ?, ?, ?, ? , ? , ?, 'active', 'img/shopLogo/logo.png')";
		Object[] list = new String[] { info.getUserID().toString(),
				info.getAgentName(), info.getAddress(), info.getPhoneNumber(),
				info.getAgentEmail(), info.getAgentWebsite(),
				info.getStartDate() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}

		sql = "update role set role = 5 where userid =?";
		try {
			templateObject.update(sql, info.getUserID());
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public boolean checkAgentNameAvail(String agentName) {
		String sql = "select * from agent_profile where agentName = ?";
		try {
			templateObject.queryForObject(sql, new Object[] { agentName }, new ShopInfoMapper());
			return true;

		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public boolean checkEmailAvail(String email) {
		String sql = "select * from agent_profile where agentEmail = ?";
		Object[] list = new String[] { email };
		try {
			templateObject.queryForObject(sql, list, new ShopInfoMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public boolean checkPhoneNumberAvail(String phoneNumber) {
		String sql = "select * from agent_profile where phonenumber = ?";
		Object[] list = new String[] { phoneNumber };
		try {
			templateObject.queryForObject(sql, list, new ShopInfoMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public ShopInfo getShopInfo(Integer userID) {
		ShopInfo info;
		String sql = "select * from agent_profile where userid = ?";
		Object[] list = new String[] { userID.toString() };
		try {
			info = templateObject.queryForObject(sql, list, new ShopInfoMapper());
			return info;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void changeShopProfile(ShopInfo info) {
		String sql = "update agent_profile set address = ?, agentemail = ?, phonenumber = ?, agentwebsite =?"
				+ " where agentid = ?";
		Object[] list = new String[] { info.getAddress(), info.getAgentEmail(),
				info.getPhoneNumber(), info.getAgentWebsite(),
				info.getAgentID().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public void updateLogo(String logo, Integer agentID) {
		String sql = "update agent_profile set logo = ? where agentid = ?";
		Object[] list = new String[] { logo, agentID.toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}

	}

	@Override
	public List<ShopInfo> listAllShop() {
		String sql = "select * from agent_profile";
		try {
			List<ShopInfo> listShop = templateObject.query(sql,
					new ShopInfoMapper());
			return listShop;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void saleProductByShop(AgentProductInfo info) {
		if(info.getContentOfShop() == null || info.getContentOfShop().equals("")){
			String sql = "insert into agent_product (productcondition, startDate, sellingprice, warranty) value (?, ?, ?, ?)";
			Object[] list1 = new String[] { info.getCondition(), info.getStartDate(),
					info.getSellingPrice().toString(), info.getWarranty().toString() };
			try {
				templateObject.update(sql, list1);
			} catch (Exception e) {
				System.out.println(e);
			}
		}else {
			String sql = "insert into agent_product (productcondition, contentofshop, startDate, sellingprice, warranty) value (?, ?, ?, ?, ?)";
			Object[] list1 = new String[] { info.getCondition(), info.getContentOfShop(),
					info.getStartDate(), info.getSellingPrice().toString(), info.getWarranty().toString() };
			try {
				templateObject.update(sql, list1);
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	@Override
	public void newList(AgentProductInfo info) {
		String sql = "insert into list (productid, agentid, listtype, liststatus) value (?, ?, ?, 1)";
		Object[] list = new String[] { info.getProductID().toString(),
				info.getAgentID().toString(), info.getListType().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {

		}

	}

	@Override
	public boolean checkListAvail(Integer productID, Integer agentID) {
		String sql = "select * from list where productid = ? and agentid = ? and liststatus = 1";
		Object[] abc = new String[] { productID.toString(), agentID.toString() };
		try {
			templateObject.queryForObject(sql, abc, new ProductListMapper());
			return true;
		} catch (Exception e) {

		}
		return false;
	}

	@Override
	public List<ProductList> getAllProductOfShop(Integer agentID) {
		String sql = "select * from list where agentid = ? and liststatus = 1";
		Object[] abc = new String[] { agentID.toString() };
		try {
			List<ProductList> listShopProduct = templateObject.query(sql, abc, new ProductListMapper());
			return listShopProduct;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public Agent_Product getAgentProductInfo(Integer listID) {
		String sql = "select * from agent_product where listid = ?";
		Object[] list = new String[] { listID.toString() };
		try {
			Agent_Product info = templateObject.queryForObject(sql, list, new Agent_ProductMapper());
			return info;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<ProductList> listProductList(Integer productID) {
		String sql = "select * from list where productid =? and liststatus = 1";
		Object[] abc = new String[] { productID.toString() };
		try {
			List<ProductList> list = templateObject.query(sql, abc, new ProductListMapper());
			return list;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public Agent_Profile getAgentProfileSellingProduct(Integer agentID) {
		String sql = "select * from agent_profile where agentid =?";
		Object[] list = new String[] { agentID.toString() };
		try {
			Agent_Profile agent = templateObject.queryForObject(sql, list, new Agent_ProfileMapper());
			return agent;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<AgentProductInfo> getAgentsSellingProduct(Integer ProductID) {
		String sql = "select * from list join agent_product on list.listid = agent_product.listid"
				+ " where list.productid = ? and list.listStatus =1 order by sellingprice limit 6";
		List<AgentProductInfo> listInfo;
		Object[] list = new String[] { ProductID.toString() };
		try {
			listInfo = templateObject.query(sql, list, new AgentProductInfoMapper());
			return listInfo;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<Agent_Profile> getAllAgent() {
		String sql = "select * from agent_profile";
		List<Agent_Profile> listProfile;
		try {
			listProfile = templateObject.query(sql, new Agent_ProfileMapper());
			return listProfile;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public ShopInfo getShopInfoByAgentID(Integer agentID) {
		String sql = "select * from agent_profile where agentid = ?";
		Object[] list = new String[] { agentID.toString() };
		try {
			ShopInfo info = templateObject.queryForObject(sql, list, new ShopInfoMapper());
			return info;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public AgentProductInfo getLowestPrice(Integer productID) {
		String sql = "select * from list join agent_product on list.listid = agent_product.listid where list.productid = ?" +
				" and list.liststatus = 1" +
				" order by agent_product.sellingprice limit 1";
		AgentProductInfo info;
		Object[] list = new String[] { productID.toString() };
		try {
			info = templateObject.queryForObject(sql, list, new AgentProductInfoMapper());
			return info;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void setDefaultInterface(Integer agentID) {
		String sql = "insert into interface (agentid, definition, banner, categoriesbar, productlist, footer, searchfield) " +
				" value(?, 'definition1', '/jsp/shopInterface/banner/DefaultBanner.jsp', " +
				" '/jsp/shopInterface/sidebar/sideBar.jsp', '/jsp/shopInterface/ProductTypeDefault.jsp', '/jsp/shopInterface/footer.jsp', '/jsp/shopInterface/SearchField.jsp' )";
		Object[] list = new String[] { agentID.toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
		
	}

	@Override
	public ShopInterface getShopInterface(Integer agentID) {
		String sql = "select * from interface where agentID = ?";
		Object[] list = new String[] { agentID.toString() };
		try {
			ShopInterface shop = templateObject.queryForObject(sql, list, new ShopInterfaceMapper());
			return shop;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public Integer getAgentID(String agentName) {
		String sql = "select * from agent_profile where agentname = ?";
		try {
			Agent_Profile profile = templateObject.queryForObject(sql, new Object[] { agentName }, new Agent_ProfileMapper());
			return profile.getAgentID();
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void editBanner(ShopInterface shop) {
		String sql = "update interface set banner = ? where agentid = ?";
		Object[] list = new String[] { shop.getBanner(), shop.getAgentID().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
		
	}

	@Override
	public void editCategoriesBar(ShopInterface shop) {
		String sql = "update interface set categoriesbar = ? where agentid = ?";
		Object[] list = new String[] { shop.getCategoriesBar(), shop.getAgentID().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
		
	}

	@Override
	public void editProductType(ShopInterface shop) {
		String sql = "update interface set productlist = ? where agentid = ?";
		Object[] list = new String[] { shop.getProductList(), shop.getAgentID().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
		
	}

	@Override
	public void editTemplate(ShopInterface shop) {
		String sql = "";
		if(shop.getDefinition().equals("definition1")){
			sql = "update interface set definition = ?,  " +
				" categoriesbar = '/jsp/shopInterface/sidebar/sideBar.jsp' where agentid = ?";
		}else if(shop.getDefinition().equals("definition2")){
			sql = "update interface set definition = ?,  " +
					" categoriesbar = '/jsp/shopInterface/sidebar/menuBar1.jsp' where agentid = ?";
		}
		Object[] list = new String[] { shop.getDefinition(), shop.getAgentID().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public List<AgentBanner> listAgentBanner(Integer agentID) {
		String sql = "select * from agentbanner where agentid = ?";
		Object[] list = new String [] { agentID.toString() };
		try {
			List<AgentBanner> listBanner = templateObject.query(sql, list, new AgentBannerMapper());
			return listBanner;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void deleteBanner(Integer agentID, String linkBanner) {
		String sql = "delete from agentbanner where agentid =? and linkbanner = ?";
		Object[] list = new String[] { agentID.toString(), linkBanner };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
		
	}

	@Override
	public void addShopBanner(Integer agentID, String linkBanner) {
		String sql = "insert into agentbanner (agentid, linkbanner) value (?, ?)";
		Object[] list = new String[] { agentID.toString(), linkBanner };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
		
	}

	@Override
	public void setBannerTittle(AgentBanner banner) {
		String sql = "update agentbanner set tittle1 = ?, tittle2 =? where agentid =? and linkbanner=?";
		Object[] list = new String[] { banner.getTittle1(), banner.getTittle2(), banner.getAgentID().toString(), banner.getLinkBanner()};
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public ProductList getProductList(Integer productID, Integer agentID) {
		String sql = "select * from list where productid = ? and agentid = ? and liststatus = 1";
		Object[] list = new String[] {productID.toString(), agentID.toString() };
		try {
			ProductList productList = templateObject.queryForObject(sql, list, new ProductListMapper());
			return productList;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void changeProductInfo(AgentProductInfo info) {
		String sql = "";
		if(info.getContentOfShop().equals("")){
			sql = "update agent_product set sellingprice =?, productcondition =?, warranty =? where listid =?";
			Object[] list = new String[] { info.getSellingPrice().toString(), info.getCondition(), 
					info.getWarranty().toString(), info.getListID().toString() };
			try {
				templateObject.update(sql, list);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}else{
			sql = "update agent_product set sellingprice =?, productcondition =?, warranty =?, contentofshop = ? where listid =?";
			Object[] list = new String[] { info.getSellingPrice().toString(), info.getCondition(), 
					info.getWarranty().toString(), info.getContentOfShop(), info.getListID().toString() };
			try {
				templateObject.update(sql, list);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		sql = "update list set listtype = ? where listid =?";
		Object[] list = new String[] {info.getListType().toString(), info.getListID().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	@Override
	public ProductList getProductList(Integer listID) {
		String sql = "select * from list where listid =? and liststatus = 1";
		Object[] list = new String[] { listID.toString() };
		try {
			ProductList productList = templateObject.queryForObject(sql, list, new ProductListMapper());
			return productList;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void makeDiscount(Discount discount) {
		String sql = "insert into discount (listid, startdate, enddate, status, discount)" +
				" value (?, ?, ?, ?, ?)";
		Object[] list = new String[] { discount.getListID().toString(), discount.getStartDate(),
				discount.getEndDate(), discount.getStatus().toString(), discount.getDiscount().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public boolean checkDiscountAvailable(Integer listID) {
		String sql = "select * from discount where listID = ? and status = 1";
		Object[] list = new String [] { listID.toString() };
		try {
			templateObject.queryForObject(sql, list, new DiscountMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public Discount getDiscount(Integer listID) {
		String sql = "select * from discount where listID = ? and status = 1";
		Object[] list = new String [] { listID.toString() };
		try {
			Discount discount = templateObject.queryForObject(sql, list, new DiscountMapper());
			return discount;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void editDiscount(Discount discount) {
		String sql = "update discount set discount = ? where listid = ? and status = 1";
		Object[] list = new String [] { discount.getDiscount().toString(), discount.getListID().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public DiscountInfo getLowestDiscount(Integer productID) {
		String sql = "select * from discount join list on discount.listid = list.listid" +
				" where list.productid = ? and discount.status = 1 order by discount limit 1";
		Object[] list = new String[] { productID.toString() };
		try {
			DiscountInfo info = templateObject.queryForObject(sql, list, new DiscountInfoMapper());
			return info;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<Integer> getListDiscountAvail() {
		String sql = "select distinct(list.productID) from list join discount " +
				"on list.listid = discount.listid where discount.status =1 ";
		try {
			List<Integer> listProductID = templateObject.query(sql, new RowMapper<Integer>() {

				@Override
				public Integer mapRow(ResultSet rs, int arg1) throws SQLException {
					Integer productID = rs.getInt("productID");
					return productID;
				}
			});
			
			return listProductID;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public AgentProductInfo getMostDiscountByProductID(Integer productID) {
		String sql = "select * from agent_product join discount on agent_product.listid = discount.listid" +
				" join list on discount.listid = list.listid where list.productid = ? and discount.status = 1" +
				" order by ((agent_product.sellingprice - discount.discount)/ agent_product.sellingprice) desc limit 1";
		Object[] list = new String[] { productID.toString() };
		try {
			AgentProductInfo info = templateObject.queryForObject(sql, list, new RowMapper<AgentProductInfo>() {

				@Override
				public AgentProductInfo mapRow(ResultSet rs, int arg1)
						throws SQLException {
					AgentProductInfo agentProductInfo = new AgentProductInfo();
					agentProductInfo.setDiscount(rs.getInt("discount"));
					agentProductInfo.setSellingPrice(rs.getInt("sellingPrice"));
					agentProductInfo.setListID(rs.getInt("listid"));
					return agentProductInfo;
				}
			});
			return info;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void endDiscount(Integer listID) {
		String sql = "update discount set status = 0 where listid = ? and status = 1";
		Object[] list = new String[] { listID.toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public Agent_Profile getAgentProfile(Integer agentID) {
		String sql = "select * from agent_profile where agentid = ?";
		Object[] list = new String[] { agentID.toString() };
		try {
			Agent_Profile profile = templateObject.queryForObject(sql, list, new Agent_ProfileMapper());
			return profile;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<AgentProductInfo> getAgentProductByProductID(Integer productID) {
		String sql = "select * from agent_product join list on list.listid = " +
				" agent_product.listid where list.liststatus = 1 and list.productid = ? ";
		Object[] list = new String[] { productID.toString() };
		try {
			List<AgentProductInfo> listInfo = templateObject.query(sql, list, new AgentProductInfoMapper());
			return listInfo;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<Discount> getAllDiscountByProductID(Integer productID) {
		String sql = "select * from discount join list on list.listid = discount.listid " +
				" where list.liststatus = 1 and discount.status = 1 and list.productid = ? ";
		Object[] list = new String[] { productID.toString() };
		try {
			List<Discount> listDiscount = templateObject.query(sql, list, new DiscountMapper());
			return listDiscount;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public void endList(Integer listID) {
		String sql = "update list set liststatus = 0 where listid = ?";
		Object[] list = new String[] { listID.toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
		
	}

	@Override
	public List<Agent_Profile> ListExactlyAgentName(String agentName) {
		String sql = "select * from agent_profile where agentname like ?";
		Object[] list = new String[] { agentName };
		try {
			List<Agent_Profile> listAgent = templateObject.query(sql, list, new Agent_ProfileMapper());
			return listAgent;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<Agent_Profile> ListSimilarAgentName(String agentName) {
		String sql = "select * from agent_profile where match(agentname) against (? in natural language mode)";
		Object[] list = new String[] { agentName };
		try {
			List<Agent_Profile> listAgent = templateObject.query(sql, list, new Agent_ProfileMapper());
			return listAgent;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}
}
